set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 200;
set hive.exec.max.dynamic.partitions.pernode = 200;

insert overwrite table jms_dm.dm_thd_door_pick_up_agent_dt
select
     base.serach_type           --查询类型
    ,base.agent_code            --代理区编码
    ,base.agent_name            --代理区名称
    ,base.virt_code             --虚拟代理区编码
    ,base.virt_name             --虚拟代理区名称
    ,base.order_num             --总单量
    ,base.recept_num            --接单量
    ,base.pick_num              --取件量
    ,base.cancel_num            --取消量
    ,base.overtime_pick_num     --超时取件量
    ,base.timely_pick_num       --取件及时量
    ,base.break_num             --爽约量
    ,base.courier_change_num    --快递员改约量
    ,base.change_other_num      --改约到其他日期量
    ,base.complaint_num         --投诉量
    ,base.complaint_again_num   --二次投诉量
    ,base.send_back_num         --自行寄回单量
    ,base.should_collect_num    --应揽收量
    ,base.timely_collect_num    --揽收及时量
    ,base.sign_num              --签收量
    ,base.pricing_num           --核价量
    ,base.timely_pricing_num    --核价及时量
    ,pick.hj_fine_amt           --核价罚款
    ,pick.qx_fine_amt           --取消率罚款
    ,pick.wdts_fine_amt         --万单投诉率罚款
    ,pick.lsbjs_fine_amt        --揽收及时率罚款
    ,pick.fine_amt              --总罚款
    ,base.timely_pick_rate      --取件及时率     --完成值
    ,base.cancel_rate           --取消率         --完成值
    ,base.break_rate            --爽约率         --完成值
    ,base.courier_change_rate   --快递员改约率   --完成值
    ,base.complaint_rate        --投诉率         --完成值
    ,base.phone_conn_rate       --电联率         --完成值
    ,base.pun_phone_conn_rate   --准时电联率     --完成值
    ,base.timely_collect_rate   --揽收及时率     --完成值
    ,base.sign_rate             --签收率         --完成值
    ,base.timely_pricing_rate   --核价及时率     --完成值
    ,base.wd_complaint_rate     --万单投诉率     --完成值
    ,round(base.hj_score  ,2) as hj_score   --核价及时率得分
    ,round(base.ls_score  ,2) as ls_score   --揽收及时率得分
    ,round(base.qx_score  ,2) as qx_score   --取消率得分
    ,round(base.wdts_score,2) as wdts_score --万单投诉率得分
    ,round(base.ts_score  ,2) as ts_score   --投诉量得分
    ,round((hj_score + ls_score + qx_score +  wdts_score),0) as total_score --总得分
    ,base.dt
from (
    select
         serach_type           --查询类型
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,dt
    from (
        select
             '虚拟代理区' as serach_type --查询类型
            ,site_code                as agent_code         --代理区编码
            ,site_name                as agent_name         --代理区名称
            ,virt_code                as virt_code          --虚拟代理区编码
            ,virt_name                as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,dt
        from jms_dm.dm_thd_door_pick_up_day_dt
        where dt = '{{ execution_date | cst_ds }}'
        and serach_type = '虚拟代理区'
        and site_name = '广深代理区'
        group by site_code
                ,site_name
                ,virt_code
                ,virt_name
                ,dt
    ) base
    union all
    select
         serach_type           --查询类型
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,dt
    from (
        select
             '代理区' as serach_type --查询类型
            ,agent_code               as agent_code         --代理区编码
            ,agent_name               as agent_name         --代理区名称
            ,virt_code                as virt_code          --虚拟代理区编码
            ,virt_name                as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,dt
        from jms_dm.dm_thd_door_pick_up_day_dt
        where dt = '{{ execution_date | cst_ds }}'
        and serach_type = '代理区'
        and site_name in ('粤东代理区','河北代理区','北京代理区','天津代理区')
        group by agent_code  --代理区编码
                ,agent_name  --代理区名称
                ,virt_code   --虚拟代理区编码
                ,virt_name   --虚拟代理区名称
                ,dt
    ) base
    union all
    select
         serach_type           --查询类型
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,dt
    from (
        select
              '代理区' as serach_type --查询类型
            ,dim.agent_code           as agent_code
            ,dim.agent_name           as agent_name
            ,dim.virt_code            as virt_code
            ,dim.virt_name            as virt_name
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,dt
        from (
            select
                *
                ,case when organ_name = '四川代理区' then '川藏代理区'
                      else organ_name end as agent_name
                ,organ_code as agent_code
            from jms_dwd.dwd_tab_door_pick_up_base_dt base
            where base.dt = '{{ execution_date | cst_ds }}'
            and data_type = 2 --代理区维度
            and organ_name not in ('粤西代理区','粤东代理区','广州代理区','东莞代理区','深圳代理区','河北代理区','苏北代理区','苏南代理区')
        ) base
        left join (
            select
                 agent_code
                ,agent_name
                ,virt_code
                ,virt_name
            from jms_dim.dim_network_whole_massage
            group by agent_code
                    ,agent_name
                    ,virt_code
                    ,virt_name
        ) dim on base.agent_name = dim.agent_name
        where base.dt = '{{ execution_date | cst_ds }}'
        and base.data_type = 2 --代理区维度
        and base.organ_name not in ('粤西代理区','粤东代理区','广州代理区','东莞代理区','深圳代理区','河北代理区','苏北代理区','苏南代理区')
        and dim.agent_name is not null
        group by dim.agent_code
                ,dim.agent_name
                ,dim.virt_code
                ,dim.virt_name
                ,base.dt
    ) base
    union all
    select
         serach_type           --查询类型
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,dt
    from (
        select
             '虚拟代理区'             as serach_type --查询类型
            ,dim.virt_code            as agent_code
            ,dim.virt_name            as agent_name
            ,null                     as virt_code
            ,null                     as virt_name
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,dt
        from (
            select
                *
                ,case when organ_name = '河北代理区'   then '河北代理区'
                      when organ_name = '苏北代理区'   then '苏北代理区'
                      when organ_name = '吉林代理区'   then '吉林代理区'
                      when organ_name = '辽宁代理区'   then '辽宁代理区'
                      when organ_name = '黑龙江代理区' then '黑龙江代理区'
                      when organ_name = '甘肃代理区'   then '甘肃代理区'
                      when organ_name = '宁夏代理区'   then '宁夏代理区'
                      when organ_name = '青海代理区'   then '青海代理区'
                      when organ_name = '贵州代理区'   then '贵州代理区'
                      when organ_name = '重庆代理区'   then '重庆代理区'
                       end as agent_name
                ,organ_code AS agent_code
            from jms_dwd.dwd_tab_door_pick_up_base_dt base
            where base.dt = '{{ execution_date | cst_ds }}'
            and data_type = 2 --代理区维度
            and organ_name not in ('粤东代理区','广州代理区','东莞代理区','深圳代理区')
        ) base
        left join (
            select
                  agent_code
                ,agent_name
                ,virt_code
                ,virt_name
            from jms_dim.dim_network_whole_massage
            group by agent_code
                    ,agent_name
                    ,virt_code
                    ,virt_name
        ) dim on base.agent_name = dim.agent_name
        where base.agent_name is not null
        group by dim.virt_code
                ,dim.virt_name
                ,base.dt
    ) base
) base
left join (
    select
         site_code
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,dt
    from jms_dm.dm_thd_door_pick_up_day_dt
    where dt = '{{ execution_date | cst_ds }}'
    and serach_type in ('代理区','虚拟代理区')
) pick on base.agent_code = pick.site_code
      and base.dt = pick.dt
distribute by dt,pmod(hash(rand()),1);




insert overwrite table jms_dm.dm_thd_door_pick_up_agent_month_dt
select
     serach_type           --查询类型
    ,agent_code            --代理区编码
    ,agent_name            --代理区名称
    ,virt_code             --虚拟代理区编码
    ,virt_name             --虚拟代理区名称
    ,order_num             --总单量
    ,recept_num            --接单量
    ,pick_num              --取件量
    ,cancel_num            --取消量
    ,overtime_pick_num     --超时取件量
    ,timely_pick_num       --取件及时量
    ,break_num             --爽约量
    ,courier_change_num    --快递员改约量
    ,change_other_num      --改约到其他日期量
    ,complaint_num         --投诉量
    ,complaint_again_num   --二次投诉量
    ,send_back_num         --自行寄回单量
    ,should_collect_num    --应揽收量
    ,timely_collect_num    --揽收及时量
    ,sign_num              --签收量
    ,pricing_num           --核价量
    ,timely_pricing_num    --核价及时量
    ,hj_fine_amt           --核价罚款
    ,qx_fine_amt           --取消率罚款
    ,wdts_fine_amt         --万单投诉率罚款
    ,lsbjs_fine_amt        --揽收及时率罚款
    ,fine_amt              --总罚款
    ,timely_pick_rate      --取件及时率     --完成值
    ,cancel_rate           --取消率         --完成值
    ,break_rate            --爽约率         --完成值
    ,courier_change_rate   --快递员改约率   --完成值
    ,complaint_rate        --投诉率         --完成值
    ,phone_conn_rate       --电联率         --完成值
    ,pun_phone_conn_rate   --准时电联率     --完成值
    ,timely_collect_rate   --揽收及时率     --完成值
    ,sign_rate             --签收率         --完成值
    ,timely_pricing_rate   --核价及时率     --完成值
    ,wd_complaint_rate     --万单投诉率     --完成值
    ,round(hj_score  ,2) as hj_score   --核价及时率得分
    ,round(ls_score  ,2) as ls_score   --揽收及时率得分
    ,round(qx_score  ,2) as qx_score   --取消率得分
    ,round(wdts_score,2) as wdts_score --万单投诉率得分
    ,round(ts_score  ,2) as ts_score   --投诉量得分
    ,round((hj_score + ls_score + qx_score + case when wdts_score > ts_score then wdts_score else ts_score end ),0) as total_score --总得分
    ,dt
from (
    select
         serach_type           --查询类型
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,dt
    from (
        select
             serach_type              as serach_type        --查询类型
            ,agent_code               as agent_code         --代理区编码
            ,agent_name               as agent_name         --代理区名称
            ,virt_code                as virt_code          --虚拟代理区编码
            ,virt_name                as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,sum(hj_fine_amt        ) as hj_fine_amt        --核价罚款
            ,sum(qx_fine_amt        ) as qx_fine_amt        --取消率罚款
            ,sum(wdts_fine_amt      ) as wdts_fine_amt      --万单投诉率罚款
            ,sum(lsbjs_fine_amt     ) as lsbjs_fine_amt     --揽收及时率罚款
            ,sum(fine_amt           ) as fine_amt           --总罚款
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,'{{ execution_date | cst_ds }}' as dt
        from jms_dm.dm_thd_door_pick_up_agent_dt
        where dt between trunc('{{ execution_date | cst_ds }}','MM') and '{{ execution_date | cst_ds }}' --月初至统计日
        group by agent_code    --代理区编码
                ,agent_name    --代理区名称
                ,virt_code     --虚拟代理区编码
                ,virt_name     --虚拟代理区名称
                ,serach_type
    ) a
) a
distribute by dt,pmod(hash(rand()),5);




insert overwrite table jms_dm.dm_thd_door_pick_up_summary_dt
select
     base.serach_type               --查询类型
    ,base.site_code                 --站点编码
    ,base.site_name                 --站点名称
    ,base.agent_code                --代理区编码
    ,base.agent_name                --代理区名称
    ,base.virt_code                 --虚拟代理区编码
    ,base.virt_name                 --虚拟代理区名称
    ,base.order_num                 --总单量
    ,base.recept_num                --接单量
    ,base.pick_num                  --取件量
    ,base.cancel_num                --取消量
    ,base.overtime_pick_num         --超时取件量
    ,base.timely_pick_num           --取件及时量
    ,base.break_num                 --爽约量
    ,base.courier_change_num        --快递员改约量
    ,base.change_other_num          --改约到其他日期量
    ,base.complaint_num             --投诉量
    ,base.complaint_again_num       --二次投诉量
    ,base.send_back_num             --自行寄回单量
    ,base.should_collect_num        --应揽收量
    ,base.timely_collect_num        --揽收及时量
    ,base.sign_num                  --签收量
    ,base.pricing_num               --核价量
    ,base.timely_pricing_num        --核价及时量
    ,base.hj_fine_amt               --核价罚款
    ,base.qx_fine_amt               --取消率罚款
    ,base.wdts_fine_amt             --万单投诉率罚款
    ,base.lsbjs_fine_amt            --揽收及时率罚款
    ,base.fine_amt                  --总罚款
    ,base.timely_pick_rate          --取件及时率
    ,base.cancel_rate               --取消率
    ,base.break_rate                --爽约率
    ,base.courier_change_rate       --快递员改约率
    ,base.complaint_rate            --投诉率
    ,base.phone_conn_rate           --电联率
    ,base.pun_phone_conn_rate       --准时电联率
    ,base.timely_collect_rate       --揽收及时率
    ,base.sign_rate                 --签收率
    ,base.timely_pricing_rate       --核价及时率
    ,base.wd_complaint_rate         --万单投诉率
    ,base.hj_score                  --核价及时率得分
    ,base.ls_score                  --揽收及时率得分
    ,base.qx_score                  --取消率得分
    ,base.wdts_score                --万单投诉率得分
    ,base.ts_score                  --投诉量得分
    ,base.total_score               --总得分
    ,mid.hj_fine_amt               as month_hj_fine_amt           --月核价罚款
    ,mid.qx_fine_amt               as month_qx_fine_amt           --月取消率罚款
    ,mid.wdts_fine_amt             as month_wdts_fine_amt         --月万单投诉率罚款
    ,mid.lsbjs_fine_amt            as month_lsbjs_fine_amt        --月揽收及时率罚款
    ,mid.fine_amt                  as month_fine_amt              --月总罚款
    ,mid.timely_pricing_rate       as month_timely_pricing_rate   --月核价及时率
    ,mid.timely_collect_rate       as month_timely_collect_rate   --月揽收及时率
    ,mid.wd_complaint_rate         as month_wd_complaint_rate     --月万单投诉率
    ,mid.cancel_rate               as month_cancel_rate           --月取消率
    ,mid.complaint_num             as month_complaint_num         --月投诉率
    ,mid.hj_score                  as month_hj_score              --月核价及时率得分
    ,mid.ls_score                  as month_ls_score              --月揽收及时率得分
    ,mid.wdts_score                as month_wdts_score            --月万单投诉率得分
    ,mid.qx_score                  as month_qx_score              --月取消率得分
    ,mid.ts_score                  as month_ts_score              --月投诉量得分
    ,mid.total_score               as month_total_score           --月总得分
    ,mid.total_not_reach_cnt       as month_total_not_reach_cnt   --当月累计不达标次数
    ,null                          as bdb_network_cnt             --当日不达标网点数
    ,null                          as month_bdb_network_cnt       --当月不达标网点数
    ,base.dt
from (
    select *
    from jms_dm.dm_thd_door_pick_up_day_dt --当天
    where dt =  '{{ execution_date | cst_ds }}'
    and serach_type not in ('代理区','虚拟代理区')
) base
left join (
    select *
    from jms_dm.dm_thd_door_pick_up_month_dt
    where dt =  '{{ execution_date | cst_ds }}'
) mid on base.serach_type = mid.serach_type
     and base.site_code   = mid.site_code --站点编码
     and base.site_name   = mid.site_name --站点名称
union all
select
     base.serach_type               --查询类型
    ,base.agent_code  as site_code  --代理区编码
    ,base.agent_name  as site_name  --代理区名称
    ,base.agent_code  as agent_code --代理区编码
    ,base.agent_name  as agent_name --代理区名称
    ,base.virt_code   as virt_code  --虚拟代理区编码
    ,base.virt_name   as virt_name  --虚拟代理区名称
    ,base.order_num                 --总单量
    ,base.recept_num                --接单量
    ,base.pick_num                  --取件量
    ,base.cancel_num                --取消量
    ,base.overtime_pick_num         --超时取件量
    ,base.timely_pick_num           --取件及时量
    ,base.break_num                 --爽约量
    ,base.courier_change_num        --快递员改约量
    ,base.change_other_num          --改约到其他日期量
    ,base.complaint_num             --投诉量
    ,base.complaint_again_num       --二次投诉量
    ,base.send_back_num             --自行寄回单量
    ,base.should_collect_num        --应揽收量
    ,base.timely_collect_num        --揽收及时量
    ,base.sign_num                  --签收量
    ,base.pricing_num               --核价量
    ,base.timely_pricing_num        --核价及时量
    ,base.hj_fine_amt               --核价罚款
    ,base.qx_fine_amt               --取消率罚款
    ,base.wdts_fine_amt             --万单投诉率罚款
    ,base.lsbjs_fine_amt            --揽收及时率罚款
    ,base.fine_amt                  --总罚款
    ,base.timely_pick_rate          --取件及时率
    ,base.cancel_rate               --取消率
    ,base.break_rate                --爽约率
    ,base.courier_change_rate       --快递员改约率
    ,base.complaint_rate            --投诉率
    ,base.phone_conn_rate           --电联率
    ,base.pun_phone_conn_rate       --准时电联率
    ,base.timely_collect_rate       --揽收及时率
    ,base.sign_rate                 --签收率
    ,base.timely_pricing_rate       --核价及时率
    ,base.wd_complaint_rate         --万单投诉率
    ,base.hj_score                  --核价及时率得分
    ,base.ls_score                  --揽收及时率得分
    ,base.qx_score                  --取消率得分
    ,base.wdts_score                --万单投诉率得分
    ,base.ts_score                  --投诉量得分
    ,base.total_score               --总得分
    ,mid.hj_fine_amt           as month_hj_fine_amt           --月核价罚款
    ,mid.qx_fine_amt           as month_qx_fine_amt           --月取消率罚款
    ,mid.wdts_fine_amt         as month_wdts_fine_amt         --月万单投诉率罚款
    ,mid.lsbjs_fine_amt        as month_lsbjs_fine_amt        --月揽收及时率罚款
    ,mid.fine_amt              as month_fine_amt              --月总罚款
    ,mid.timely_pricing_rate   as month_timely_pricing_rate   --月核价及时率
    ,mid.timely_collect_rate   as month_timely_collect_rate   --月揽收及时率
    ,mid.wd_complaint_rate     as month_wd_complaint_rate     --月万单投诉率
    ,mid.cancel_rate           as month_cancel_rate           --月取消率
    ,mid.complaint_num         as month_complaint_num        --月投诉率
    ,mid.hj_score              as month_hj_score              --月核价及时率得分
    ,mid.ls_score              as month_ls_score              --月揽收及时率得分
    ,mid.wdts_score            as month_wdts_score            --月万单投诉率得分
    ,mid.qx_score              as month_qx_score              --月取消率得分
    ,mid.ts_score              as month_ts_score              --月投诉量得分
    ,mid.total_score           as month_total_score           --月总得分
    ,null as month_total_not_reach_cnt
    ,pro.bdb_network_cnt           as bdb_network_cnt             --当日不达标网点数
    ,pro_mid.month_bdb_network_cnt as month_bdb_network_cnt       --当月不达标网点数
    ,base.dt
from (
    select *
    from jms_dm.dm_thd_door_pick_up_agent_dt --当天
    where dt =  '{{ execution_date | cst_ds }}'
) base
left join (
    select
         dim.virt_code as virt_code      --虚拟代理区
        ,dim.virt_name as virt_name      --虚拟代理区
        ,count(distinct case when total_score < 90 then base.site_code else 0 end) as bdb_network_cnt --当日不达标次数
    from jms_dm.dm_thd_door_pick_up_day_dt base
    left join jms_dim.dim_network_whole_massage dim
       on dim.code = base.site_code
    where base.dt = '{{ execution_date | cst_ds }}'
    and base.serach_type = '网点'
    group by dim.virt_code
            ,dim.virt_name
    union all
    select
         dim.agent_code as virt_code      --虚拟代理区
        ,dim.agent_name as virt_name      --虚拟代理区
        ,count(distinct case when total_score < 90 then base.site_code else 0 end) as bdb_network_cnt --当日不达标次数
    from jms_dm.dm_thd_door_pick_up_day_dt base
    left join jms_dim.dim_network_whole_massage dim
       on dim.code = base.site_code
    where base.dt = '{{ execution_date | cst_ds }}'
    and base.serach_type = '网点'
    group by dim.agent_code
            ,dim.agent_name
) pro on base.agent_name = pro.virt_name
left join (
    select *
    from jms_dm.dm_thd_door_pick_up_agent_month_dt
    where dt =  '{{ execution_date | cst_ds }}'
) mid on base.serach_type = mid.serach_type
     and base.agent_code  = mid.agent_code --站点编码
     and base.agent_name  = mid.agent_name --站点名称
left join (
    select
         dim.virt_code as virt_code      --虚拟代理区
        ,dim.virt_name as virt_name      --虚拟代理区
        ,count(distinct case when total_score < 90 then base.site_code else 0 end) as month_bdb_network_cnt --当月不达标次数
    from jms_dm.dm_thd_door_pick_up_month_dt base
    left join jms_dim.dim_network_whole_massage dim
       on dim.code = base.site_code
    where base.dt =  '{{ execution_date | cst_ds }}'
    and base.serach_type = '网点'
    group by dim.virt_code
            ,dim.virt_name
    union all
    select
         dim.agent_name as virt_code      --虚拟代理区
        ,dim.agent_name as virt_name      --虚拟代理区
        ,count(distinct case when total_score < 90 then base.site_code else 0 end) as month_bdb_network_cnt --当月不达标次数
    from jms_dm.dm_thd_door_pick_up_month_dt base
    left join jms_dim.dim_network_whole_massage dim
       on dim.code = base.site_code
    where base.dt =  '{{ execution_date | cst_ds }}'
    and base.serach_type = '网点'
    group by dim.agent_name
            ,dim.agent_name
) pro_mid on base.agent_name = pro_mid.virt_name
distribute by dt,pmod(hash(rand()),5);